package com.limao.DBProvider;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.httpclient.NameValuePair;

import com.Szjcyh.Until.Api;
import com.Szjcyh.Until.Api2;
import com.Szjcyh.Until.Api3;
import com.Szjcyh.Until.QueryJWModelData;
import com.Szjcyh.Until.QueryModel;
import com.Szjcyh.Until.QueryZDModel;
import com.Szjcyh.Until.QueryZDModelData;
import com.Szjcyh.Until.ResultModel;
import com.Szjcyh.Until.ResultModel2;
import com.Szjcyh.Until.ResultModel3;
import com.Szjcyh.Until.TestXml2Json;
import com.alibaba.fastjson.JSON;
import com.limao.CacheManager.BaseDataCacheManager;
import com.limao.CacheManager.CrowdSmallCompanyCacheManager;
import com.limao.CacheManager.CrowdUserAuthCache;
import com.limao.CacheManager.user_parentCacheManager;
import com.limao.CacheManager.user_rmbCacheManager;
import com.limao.CacheManager.usernumCacheManager;
import com.limao.DBDataClass.CrowdSmallCompanyClass;
import com.limao.DBDataClass.CrowdUserAuthClass;
import com.limao.DBDataClass.CrowdUserParentClass;
import com.limao.DBDataClass.MobileStateDataClass;
import com.limao.DBDataClass.userRmbDataClass;
import com.limao.DbConnection.DbConnectionManager;
import com.limao.Model.LzPluginException;
import com.limao.Tool.CacheClass;
import com.limao.push.InviteCutThread;
import com.ndktools.javamd5.Mademd5;

public class CrowdUserAuthProvider {


	private static class DB_user_ProviderContainer {
		private static CrowdUserAuthProvider instance = new CrowdUserAuthProvider();
	}

	public static CrowdUserAuthProvider getInstance() {
		return DB_user_ProviderContainer.instance;
	}

	// 定义系统表的name与操作语句
	private static final String authTable = "cd_user_auth";

	private static final String parent_table = "cd_user_parent";

	private static final String card_table= "cd_user_card";

	// 取会员注册信息 by id
	private static final String cd_user_auth_LOAD_byID = "{call getUserInfoById(?)}";

	private static final String cd_user_auth_LOAD_byMobile = "{call getUserInfoByMobile(?)}";;
	
	//手机号是否被用
	private static final String cd_user_auth_isHave_byMobile = "SELECT tb1.userid  FROM " + authTable + " tb1  WHERE tb1.mobile=?  ";

	// 增加用户
	private static final String INSERT_USER_auth_Usename = "INSERT INTO "
			+ authTable
			+ " (userid,mobile,password,password2,username,introduceId,iscompanyid,ctime,pwd,userTest) VALUES (?,?,?,?,?,?,?,?,?,?)";

	private static final String INSERT_USER_Rmb = "INSERT INTO cd_user_rmb (userid,rank) VALUES (?,?)";	
	
	private static final String INSERT_USER_Card = "INSERT INTO " + card_table + " (userid) VALUES (?)";	
	
	
	private static final String INSERT_USER_parent = "INSERT INTO "
			+ parent_table + " (userid,companyuserid,pid0,pid1,pid2,time,pid3,pid4) VALUES (?,?,?,?,?,?,?,?)";
	
	

	


	// 修改资料(绑定其它号)
	private static final String lz_user_UPDATE_Auth = "UPDATE "	+ authTable	+ " SET username=? WHERE userid=?";
	
	private static final String lz_user_UPDATE_Auth_avatar = "UPDATE "+ authTable+ " SET avatar=? WHERE userid=?";
	
	private static final String lz_user_UPDATE_Auth_Base = "UPDATE "+ authTable+ " SET sign=? WHERE userid=?";
	
	private static final String lz_user_UPDATE_Auth_Bank = "UPDATE "+ card_table+ " SET cardtype=?,cardno=?,cardname=?,cardbank=?,cardarea=? WHERE userid=?";
	

	
	// 修改资料(更换手机号)
	private static final String lz_user_UPDATE_MoOBILE = "UPDATE " + authTable
			+ " SET mobile=? WHERE userid=?";

	// 密码重置 by id
	private static final String lz_user_UPDATE_PASSWORD_ById = "UPDATE "
			+ authTable + " SET password=?,password2=? WHERE userid=?";
	// 密码重置 by mobile
	private static final String lz_user_UPDATE_PASSWORD_ByMobile = "UPDATE "
			+ authTable
			+ " SET password=?,password2=? WHERE mobile=?";
	// 客户端登陆验证
	private static final String lz_Login_Mobile = "{call authUser(?,?)}"; 

	// 初始化
	private CrowdUserAuthProvider() {}

	
	public CrowdUserAuthClass RsToCrowdUserAuthClass(ResultSet rs) throws SQLException
	{
		CrowdUserAuthClass tmp = new CrowdUserAuthClass();

		tmp.userid = rs.getString("userid"); 						//用户id
	    tmp.mobile = rs.getString("mobile");					//人物手机号
	    tmp.username = rs.getString("username");                     //人物呢称
	    tmp.avatar = rs.getString("avatar");					//人物头像
	    tmp.isdisabled = rs.getString("isdisabled");
	    tmp.rmbstate = rs.getString("rmbstate");
	    tmp.sign = rs.getString("sign");
	    tmp.introduceId = rs.getString("introduceId");
	    tmp.ctime = rs.getString("ctime");
	    tmp.utime = rs.getString("utime");
	    tmp.isshopuser = rs.getString("isshopuser");
	    tmp.iscompanyid = rs.getString("iscompanyid");
	    
	    tmp.applyid = rs.getString("applyid");
	    tmp.applystate = rs.getString("applystate");
	    
	    
	    
	    
	    tmp.isvruser = rs.getString("isvruser");
	    tmp.signtime = rs.getString("signtime");
	    tmp.viplevel = rs.getString("viplevel");
	    tmp.vipdate = rs.getString("vipdate");
	    tmp.vipdateStr = getVipStr(tmp.viplevel,tmp.vipdate);
    
	    tmp.istest = rs.getString("istest");
	    tmp.userTest = rs.getString("userTest");
	    
	    
	    tmp.uid= rs.getString("uid");
	    tmp.mt_token= rs.getString("mttoken");
	    
	    
		tmp.principlelrmb = rs.getString("principlelrmb");
		tmp.nowrmb  = rs.getString("nowrmb");
		tmp.totalbuyrmb = rs.getString("totalbuyrmb");
		tmp.totalbuyscore = rs.getString("totalbuyscore");
		tmp.totalwithinrmb = rs.getString("totalwithinrmb");
		tmp.total_mannum = rs.getString("total_mannum");
		tmp.rank = rs.getString("rank");
		tmp.redbagkey = rs.getString("redbagkey");
		tmp.vrnum = rs.getString("vrnum");
		tmp.prestigevalue= rs.getString("prestigevalue");
		tmp.rankprestigevalue = rs.getString("rankprestigevalue");
		tmp.totalinvestrmb= rs.getString("totalinvestrmb");
		tmp.virtualcoin= rs.getString("virtualcoin");
		tmp.virtualcoinice= rs.getString("virtualcoinice");
		tmp.maxvirtualcoin= rs.getString("maxvirtualcoin");
		int tempnum=CacheClass.getIntValue(CacheClass.getSYS_BaseDataCacheManager().Load_Param("tempnum").trim());
		tmp.tempnum=tempnum+"";
		
		
		tmp.cardtype = rs.getInt("cardtype") + "";// 创建日期
		tmp.cardno = rs.getString("cardno");// 创建日期
		tmp.cardname = rs.getString("cardname");// 创建日期
		tmp.cardbank = rs.getString("cardbank") ;// 创建日期
		tmp.cardarea = rs.getString("cardarea") ;
		
		
	    tmp.invitename = rs.getString("invitename");
	    tmp.inviteuserid = rs.getString("inviteuserid");
		tmp.qrcodeurl = CacheClass.getShareUrl()+"Reg?m=1&introduceId="+tmp.userid;
		//是否是办事处管理员 0不是 非0是
		tmp.issmallcompanyid=CrowdSmallCompanyProvider.getInstance().loadSmallCompanyByuserid(tmp.userid)+"";
		

		tmp.manowtotalrmb= VrGoodsUserNumProvider.getInstance().getUserVrGoodsNum(tmp.userid)+"";
		
		
		
		return tmp;
		
	}
	
	
	private String getVipStr(String viplevelstr,String vipdatestr)
	{
		
		int vipdate=CacheClass.getIntValue(vipdatestr);
		int viplevel=CacheClass.getIntValue(viplevelstr);
		if(viplevel==0 || vipdate==0)
		{
			return "非VIP";
		}
		else
		{
			int todaydate=CacheClass.getDateAfterDay2(new Date(), 0);
			if(vipdate<todaydate)
			{
				return "非VIP";
			}
			else
			{
				switch(viplevel)
				{
				case 1:
					return " 【有效至" +vipdate+"】";
				case 2:
					return " 【有效至" +vipdate+"】";
				case 3:
					return " 【有效至" +vipdate+"】";		
				case 4:
					return " 【有效至" +vipdate+"】";
				case 5:
					return " 【有效至" +vipdate+"】";
				default:
					return "";
				}
				
				
			}
			
		}
	}
	
	public String getVipLevelStr(String vipdatestr,String level)
	{
		
		int vipdate=CacheClass.getIntValue(vipdatestr);
		if(vipdate==0)
		{
			return "非经销商";
		}
		else
		{
			int todaydate=CacheClass.getDateAfterDay2(new Date(), 0);
			if(vipdate<todaydate || CacheClass.getIntValue(level)==0)
			{
				return "非经销商";
			}
			else
			{
				return level+"级  【有效至" +vipdate+"】";
			}
			
		}
	}
	
	
	//&新鲜宝    客户端登陆验证
	public CrowdUserAuthClass login_User_AuthBy(String mobile, String password)
			throws LzPluginException {
		CrowdUserAuthClass tmp = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select userid,password,isdisabled from cd_user_auth where mobile=?");
			pstmt.setLong(1, CacheClass.getLongValue(mobile));
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				if(password.equalsIgnoreCase(rs.getString("password")))
				{
					int isdisabled=rs.getInt("isdisabled");
					if(isdisabled==1)
						throw new LzPluginException("您的帐号被禁用");
					else
						tmp=login1_User_AuthBy(mobile, password);
				}
				else
					throw new LzPluginException("[密码]不正确");
			}
			else
			{
				throw new LzPluginException("[用户名]不存在");
			}
		} catch (SQLException e) {
			tmp=null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

		return tmp;
	}
	
	//&新鲜宝   登陆取用户信息
	private CrowdUserAuthClass login1_User_AuthBy(String mobile, String password)
			throws LzPluginException {
		CrowdUserAuthClass tmp = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall(lz_Login_Mobile);
			pstmt.setLong(1, CacheClass.getLongValue(mobile));
			pstmt.setString(2, password);
			boolean hadResults = pstmt.execute();
			if (hadResults) {
				rs = pstmt.getResultSet();
				if (rs != null && rs.next()) {

					tmp=RsToCrowdUserAuthClass(rs);
					
				}
			}

		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}

		return tmp;
	}

	//&新鲜宝      查询用户资料据,根据id
	public CrowdUserAuthClass load_User_Auth_ById(int id) {
		CrowdUserAuthClass tmp =null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
		
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall(cd_user_auth_LOAD_byID);
			pstmt.setInt(1, id);

			boolean hadResults = pstmt.execute();
			if (hadResults) {
				rs = pstmt.getResultSet();
				if (rs != null && rs.next()) {
					tmp=RsToCrowdUserAuthClass(rs);
				}
			}

		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return tmp;
	}
	

    //&新鲜宝  密码是否正确
    public String getPwdMd5(int userid)  {
    	String pwd="";
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
		
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("SELECT pwd  FROM " + authTable + " WHERE userid=?" );
			pstmt.setInt(1, userid);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				pwd=rs.getString("pwd");
			}
			int isZDTrade=CacheClass.getIntValue(BaseDataCacheManager.getInstance().Load_Param("isZDTrade"));
			if(isZDTrade==0)  //金城系统
			{

			}
			else  //郑大系统
			{
				if(!CacheClass.isEmpty(pwd))	
					pwd=DigestUtils.md5Hex(pwd).toUpperCase();
			}

		} catch (Exception e) {
			pwd = "";
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return pwd;
	}
	
    //&新鲜宝  密码是否正确
    public boolean passIsValid(int id,String pass)  {
    	boolean flag=false;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
		
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("SELECT password  FROM " + authTable + " WHERE userid=?" );
			
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				if(pass.equalsIgnoreCase(rs.getString("password")))
					flag=true;
			}
				

		} catch (Exception e) {
			flag = false;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return flag;
	}
    
	//&新鲜宝   查询用户资料据,根据mobile
	public CrowdUserAuthClass load_User_Auth_ByMobile(String mobile) {
		CrowdUserAuthClass tmp = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall(cd_user_auth_LOAD_byMobile);
			pstmt.setLong(1, CacheClass.getLongValue(mobile));
			boolean hadResults = pstmt.execute();
			if (hadResults) {
				rs = pstmt.getResultSet();
				if (rs != null && rs.next()) {

					tmp=RsToCrowdUserAuthClass(rs);
					
				}
			}

		} catch (Exception e) {
			tmp = null;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return tmp;
	}
	
	//&新鲜宝  手机号是否被绑定或注册过
	public boolean isHave(String mobile)
	{
		boolean flag = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(cd_user_auth_isHave_byMobile);
			pstmt.setLong(1, CacheClass.getLongValue(mobile));
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag=true;
			}

		} catch (Exception e) {
			flag = true;
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return flag;
	}
	
	//&新鲜宝  批量手机号是否已注册过
	 public List<MobileStateDataClass> getMobileState(String mobileList)
	 {
		 List<MobileStateDataClass> list=new ArrayList<MobileStateDataClass>();
		 List<tmpClass> tmplist=new ArrayList<tmpClass>();
		 Connection con = null;
		 PreparedStatement pstmt = null;
			ResultSet rs = null;
			String[] moblieAr=mobileList.split(",");
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("select mobile from cd_user_auth where mobile in ("+mobileList+") ");
				rs = pstmt.executeQuery();
				while (rs.next()) {
					tmpClass obj=new tmpClass();
					obj.mobile=rs.getString("mobile");
					tmplist.add(obj);
				}

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
			
			for(String m:moblieAr)
			{
				MobileStateDataClass tmp=new MobileStateDataClass();
				tmp.mobile=m;
				tmp.state="0";
				for(tmpClass obj:tmplist)
				{
					if(m.equalsIgnoreCase(obj.mobile))
					{
						tmp.state="1";
						break;
					}
				}
				list.add(tmp);
			}
			
			return list;
	 }
	 
	 class tmpClass
	 {
			String mobile;
	 }
	 
	//&新鲜宝  查询用户密码,根据id
	public String getUserOldPassWord(int id) throws LzPluginException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String str=null;
		try {

			con = DbConnectionManager.getConnection();
			pstmt = con.prepareCall(cd_user_auth_LOAD_byID);
			pstmt.setInt(1, id);
			boolean hadResults = pstmt.execute();
			if (hadResults) {
				rs = pstmt.getResultSet();
				if (rs != null && rs.next()) {
					str= rs.getString("password");
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		
		if(!CacheClass.isEmpty(str))
			return str;
		else
			throw new LzPluginException("取不到该用户的旧密码");
	}
	
	//&新鲜宝  修改用户密码 by id
	public CrowdUserAuthClass UPDATA_User_auth_password_ById(int userid,String password, String oldpassword,String codepassword) throws LzPluginException

	{
		CrowdUserAuthClass tmp = null;
		CrowdUserAuthClass tmp2 = null;
		tmp2 = CrowdUserAuthCache.getInstance().getUser(userid);

		// 如果没有，则新建用户Auth记录
		if (tmp2 != null) {
			String dboldpassword = getUserOldPassWord(userid);

			if (!oldpassword.equalsIgnoreCase(dboldpassword)) {
				throw new LzPluginException("旧密码不正确");
			}
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(lz_user_UPDATE_PASSWORD_ById);
				pstmt.setString(1, password);
				pstmt.setString(2, codepassword);
				pstmt.setInt(3, userid);
				boolean success = pstmt.executeUpdate() == 1;
				if (success) {
					tmp = new CrowdUserAuthClass();
					tmp = tmp2;
				}

			} catch (Exception e) {
				tmp = null;
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}
		if(tmp!=null)
			LoginAuthForTrade(userid+"", true,codepassword);
		
		
		tmp2 = null;
		return tmp;
	}

	//&新鲜宝  修改用户密码 by mobile
	public CrowdUserAuthClass UPDATA_User_auth_password_ByMobile(String mobile,	String password,String codepassword)   throws LzPluginException {
		CrowdUserAuthClass tmp = null;
		CrowdUserAuthClass tmp2 = null;
		tmp2 = CrowdUserAuthCache.getInstance()
				.getUserBymobile(mobile);

		// 如果没有，则新建用户Auth记录
		if (tmp2 != null) {
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(lz_user_UPDATE_PASSWORD_ByMobile);
				pstmt.setString(1, password);
				pstmt.setString(2, codepassword);
				pstmt.setLong(3, CacheClass.getLongValue(mobile));
				boolean success = pstmt.executeUpdate() == 1;
				if (success) {
					tmp = new CrowdUserAuthClass();
					tmp = tmp2;
				} else {
					throw new LzPluginException("没有手机为" + mobile + "的用户");
				}

			} catch (Exception e) {
				tmp = null;
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}
		if(tmp!=null)
			LoginAuthForTrade(tmp.userid+"", true,codepassword);
		
		tmp2 = null;
		return tmp;
	}

	//&新鲜宝  修改用户绑定 手机号
	public CrowdUserAuthClass UPDATA_User_auth_mobile(String mobile,int id)  {
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(id);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(lz_user_UPDATE_MoOBILE);
				pstmt.setLong(1, CacheClass.getLongValue(mobile.trim()));
				pstmt.setInt(2, id);
				if(pstmt.executeUpdate()==1)
					tmp2.mobile=mobile;

			} catch (Exception e) {
				tmp2=null;
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}

		return tmp2;
	}
	
	//&新鲜宝    修改用户 头像
	public CrowdUserAuthClass UPDATA_User_auth_Avatar(int id,String avatar)  {
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(id);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(lz_user_UPDATE_Auth_avatar);
				pstmt.setString(1, avatar);
				pstmt.setInt(2, id);
				pstmt.executeUpdate();
				
				tmp2.avatar=avatar;

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}

		return tmp2;
	}

	
	//&新鲜宝    修改用户 呢称
	public CrowdUserAuthClass UPDATA_User_auth_name(int id,String name)  {
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(id);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(lz_user_UPDATE_Auth);
				pstmt.setString(1, name);
				pstmt.setInt(2, id);
				pstmt.executeUpdate();
				
				tmp2.username=name;

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}

		return tmp2;
	}


	//&新鲜宝   修改用户 其它基本信息
	public CrowdUserAuthClass UPDATA_User_auth_Base(int id,String sign)  {
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(id);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement(lz_user_UPDATE_Auth_Base);
				if (CacheClass.isEmpty(sign)) {
					pstmt.setNull(1, Types.VARCHAR);
				} else {
					pstmt.setString(1, sign.trim());
				}				
	
				pstmt.setInt(2, id);
				pstmt.executeUpdate();
				
				CrowdUserAuthCache.getInstance().ClearObjectCache(id+"");
				tmp2=CrowdUserAuthCache.getInstance().getUser(id);

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}

		return tmp2;
	}


	//&增加用户 用户名注册
	public CrowdUserAuthClass createUsernmae(String mobile,String password,String introduceId,String password2) throws LzPluginException {
		boolean abortTransaction = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		CrowdUserAuthClass tmp = null;
		
		String userid="";
		
		// Auth表中是否有记录
		tmp = CrowdUserAuthCache.getInstance().getUserBymobile(mobile);
		if(tmp!=null)
		{
			throw new LzPluginException("用户已存在");
		}
		
		String iscompanyid=null;
		int userTest=0;
		
		if(CacheClass.isEmpty(introduceId))
		{
			iscompanyid="1"; //默认为总公司
		}
		else
		{
			if(CacheClass.getIntValue(introduceId)<500)// 少于500认定为分公司id
			{
				iscompanyid=introduceId;
				introduceId=null;
			}
			else
			{
				CrowdUserAuthClass introduceAuth =CrowdUserAuthCache.getInstance().getUser(CacheClass.getIntValue(introduceId));
				if(introduceAuth==null)
				{
					throw new LzPluginException("推荐人不存在");
				} 
				else if(CacheClass.getIntValue(introduceAuth.iscompanyid)==0)
				{
					iscompanyid="1"; //默认为总公司
					userTest=CacheClass.getIntValue(introduceAuth.userTest);
				}
				else
				{
					iscompanyid=introduceAuth.iscompanyid;
					userTest=CacheClass.getIntValue(introduceAuth.userTest);
				}
			}
		
		}
		// 如果没有，则新建用户Auth记录
		if (tmp == null) {
			System.out.println("注册-得ID号");
			// 获得本次的ID号
			int id = usernumCacheManager.getInstance().ModifiedObjectCache();
			userid=id+"";
			System.out.println("注册-开始auth表1");
			
			try {
				long time = new Date().getTime();
				con = DbConnectionManager.getTransactionConnection();
				pstmt = con.prepareStatement(INSERT_USER_auth_Usename);
				pstmt.setInt(1,id);
				pstmt.setLong(2, CacheClass.getLongValue(mobile.trim()));
				pstmt.setString(3, password);
				pstmt.setString(4, password2);
				pstmt.setString(5, CacheClass.MobileStar(mobile));

				if (!CacheClass.isEmpty(introduceId)) {
					pstmt.setString(6, introduceId);
				} else {
					pstmt.setNull(6, Types.VARCHAR);
				}
				
				if(!CacheClass.isEmpty(iscompanyid))
				{
					pstmt.setString(7, iscompanyid);
				}
				else
				{
					pstmt.setNull(7, Types.VARCHAR);
				}
				
				pstmt.setLong(8, time);
				pstmt.setString(9, password);
				pstmt.setInt(10, userTest);
				
				pstmt.execute();


				// Rmb-id
				DbConnectionManager.fastcloseStmt(pstmt);
				pstmt = con.prepareStatement(INSERT_USER_Rmb);
				pstmt.setInt(1, id);
				pstmt.setInt(2, id);
				pstmt.execute();
				
				//card-id
				DbConnectionManager.fastcloseStmt(pstmt);
				pstmt = con.prepareStatement(INSERT_USER_Card);
				pstmt.setInt(1, id);
				pstmt.execute();		
				
				
				// parent-id
				if(!CacheClass.isEmpty(introduceId)){ //cd_user_parent
					//求得推荐人父二级的id
					CrowdUserParentClass tmp0=user_parentCacheManager.getInstance().load_user_Parent(CacheClass.getIntValue(introduceId));
					//用户关系表
					DbConnectionManager.fastcloseStmt(pstmt);
					pstmt = con.prepareStatement(INSERT_USER_parent);
					pstmt.setInt(1, id);
					pstmt.setInt(2, CacheClass.getIntValue(iscompanyid));
					pstmt.setInt(3, CacheClass.getIntValue(tmp0.userid));
					pstmt.setInt(4, CacheClass.getIntValue(tmp0.pid0));
					pstmt.setInt(5, CacheClass.getIntValue(tmp0.pid1));
					pstmt.setLong(6, time);
					pstmt.setInt(7, CacheClass.getIntValue(tmp0.pid2));
					pstmt.setInt(8, CacheClass.getIntValue(tmp0.pid3));
					
					pstmt.execute();		
				}
				else
				{
					//用户关系表
					DbConnectionManager.fastcloseStmt(pstmt);
					pstmt = con.prepareStatement(INSERT_USER_parent);
					pstmt.setInt(1, id);
					pstmt.setInt(2, CacheClass.getIntValue(iscompanyid));
					pstmt.setInt(3, 0);
					pstmt.setInt(4, 0);
					pstmt.setInt(5, 0);
					pstmt.setLong(6, time);
					pstmt.setInt(7, 0);
					pstmt.setInt(8, 0);
					pstmt.execute();
				}
	

				System.out.println("注册-set表  ");

			} catch (Exception e) {
				tmp = null;
				abortTransaction = true;
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeTransactionConnection(pstmt, con,abortTransaction);
			}

		}
		System.out.println("注册-表 结束");
		// 注册成功后删除mobile code的记录
		if (!abortTransaction) {
			
			
	/*		
			
			//交易所注册
			int isZDTrade=CacheClass.getIntValue(BaseDataCacheManager.getInstance().Load_Param("isZDTrade"));
			if(isZDTrade==0)  //金城系统
			{
				QueryModel queryParameters=new QueryModel();
				queryParameters.weixinid= userid;
				queryParameters.mobile= mobile;
				queryParameters.loginpwd= DigestUtils.shaHex(password);
				queryParameters.moneypwd= DigestUtils.shaHex(password);
				queryParameters.username= CacheClass.MobileStar(mobile);
				queryParameters.idcode= userid;

				String jsonStr=JSON.toJSONString(queryParameters);
				ResultModel result=null;
				try {
					result=Api.buildRequest(CacheClass.getMwUrl()+"reg",jsonStr);
				} catch (Exception e) {
					result=null;
					e.printStackTrace();
				}
  				if(result==null || CacheClass.getIntValue(result.retcode)!=0)
				{
					if(result!=null)
					{
						System.out.println( "出错,注册失败,retcode="+result.retcode+":"+result.GetRetMsg());
					}
					else
					{
						System.out.println( "出错,注册失败,retcode=null");
					}
				}
				else //注册交易系统成功，用户表追加uid值
				{
					System.out.println("交易所注册成功");
					CrowdUserAuthCache.getInstance().updateAuthForUid(userid,result.uid,result.useraccount,password);
				}
			}
			else //郑大系统
			{
				CrowdSmallCompanyClass obj=CrowdSmallCompanyCacheManager.getInstance().loadSmallCompanyByid(iscompanyid);
				if(obj!=null && !CacheClass.isEmpty(obj.marketparentid))
				{
					QueryZDModelData data=new QueryZDModelData();
					data.NF241FB=mobile;
					data.NF261FC=CacheClass.MobileStar(mobile);
					data.NF2Q1FD=DigestUtils.md5Hex(password).toUpperCase();
					data.NF2B1FE=mobile;
					data.NF291FF=userid;
					data.NF2H1FG=obj.marketparentid;
					
					QueryZDModel mode= new QueryZDModel();
					mode.NF2S="NF2BVK";
					mode.NF3A=data;
					mode.NF3B1FL=userid;
					
					String jsonStr=JSON.toJSONString(mode);
					
					Map<String, String> directivesVal=new HashMap<String, String>();
					directivesVal.put("directives", "["+jsonStr+"]");
					
					NameValuePair[] formParams= Api.generatNameValuePair(directivesVal);	

					ResultModel2 result=null;
					try {
						result=Api2.buildRequest(CacheClass.getMwUrl(),formParams);
					} catch (Exception e) {
						result=null;
						e.printStackTrace();
					}
					if(result==null || result.getRetCode()!=0)
					{
						if(result!=null)
						{
							System.out.println( "出错,注册失败,retcode="+result.NF3C+":"+result.NF34);
						}
						else
						{
							System.out.println( "出错,注册失败,retcode=null");
						}
					}
					else //注册交易系统成功，用户表追加uid值
					{
						System.out.println("交易所注册成功");
						CrowdUserAuthCache.getInstance().updateAuthForUid(userid,mobile,password);
					}
				}
				
				
			}*/
			
				
			
			tmp = CrowdUserAuthCache.getInstance().getUserBymobile(mobile);
			if(!CacheClass.isEmpty(mobile))
				CacheClass.getMobileCodeCacheManger().deleteMobileCode(mobile);
			
			//邀请人的团队人数加1
			if(!CacheClass.isEmpty(introduceId))
			{
				userRmbDataClass uu=user_rmbCacheManager.getInstance().update_User_Mannum(CacheClass.getIntValue(introduceId));
				if(uu!=null)
					CrowdUserAuthCache.getInstance().ClearObjectCache(introduceId);	
			}
			
			//活跃值增加
			InviteCutThread  prestige=new InviteCutThread(CacheClass.getIntValue(userid),0);
			prestige.start();
			
		}
		
		return tmp;
	}
	
	//注册失败
	public void deleteUsernmae(String userid)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			int id=CacheClass.getIntValue(userid);
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("delete from cd_user_auth where userid=?");
			pstmt.setInt(1,id);
			pstmt.execute();


			// Rmb-id
			DbConnectionManager.fastcloseStmt(pstmt);
			pstmt = con.prepareStatement("delete from cd_user_rmb where userid=?");
			pstmt.setInt(1, id);
			pstmt.execute();
			
			//card-id
			DbConnectionManager.fastcloseStmt(pstmt); 
			pstmt = con.prepareStatement("delete from cd_user_card where userid=?");
			pstmt.setInt(1, id);
			pstmt.execute();		
			
			
			// parent-id
			DbConnectionManager.fastcloseStmt(pstmt);
			pstmt = con.prepareStatement("delete from cd_user_parent where userid=?");
			pstmt.setInt(1, id);
			pstmt.execute();		

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}
	


	//&新鲜宝    修改提现绑定
	public CrowdUserAuthClass UPDATA_User_auth_Bank(int id,int cardtype,String cardno,String cardname,String cardbank,String cardarea)  throws LzPluginException {
		
		boolean abortTransaction = false;
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(id);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getTransactionConnection();
				pstmt = con.prepareStatement(lz_user_UPDATE_Auth_Bank);
				pstmt.setInt(1, cardtype);
				
				if (CacheClass.isEmpty(cardno)) {
					pstmt.setNull(2, Types.VARCHAR);
				} else {
					pstmt.setString(2, cardno.trim());
				}
				
				if (CacheClass.isEmpty(cardname)) {
					pstmt.setNull(3, Types.VARCHAR);
				} else {
					pstmt.setString(3, cardname.trim());
				}
				
				
	
				if (CacheClass.isEmpty(cardbank)) {
					pstmt.setNull(4, Types.VARCHAR);
				} else {
					pstmt.setString(4, cardbank.trim());
				}
				if (CacheClass.isEmpty(cardarea)) {
					pstmt.setNull(5, Types.VARCHAR);
				} else {
					pstmt.setString(5, cardarea.trim());
				}
				
				pstmt.setInt(6, id);
				pstmt.executeUpdate();
				tmp2.cardtype=cardtype+"";
				tmp2.cardno=cardno.trim();
				tmp2.cardname=cardname.trim();
				tmp2.cardbank=cardbank;
				tmp2.cardarea=cardarea;
				

			} catch (SQLException e) {
				tmp2=null;
				abortTransaction = true;
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeTransactionConnection(rs,pstmt, con,abortTransaction);
			}
		}

		return tmp2;
	}
	
    
	//&新鲜宝	理财帐户开启
	public CrowdUserAuthClass UPDATA_User_auth_Isvruser(int userid)  {
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(userid);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("UPDATE cd_user_auth SET isvruser=1 WHERE userid=?;");
				pstmt.setInt(1, userid);
				if(pstmt.executeUpdate()==1)
				{
					tmp2.isvruser="1";
					//理财帐户初始化
					DbConnectionManager.fastcloseStmt(pstmt);
					pstmt = con.prepareStatement("insert into vr_user_rmb (userid,vrprinciplelrmb,vrcashgiftrmb1,vrcashgiftrmb2,vrnowrmb," +
							" vrnowcashgiftrmb1,vrnowcashgiftrmb2,vrpreorderrmb) values(?,?,?,?,?,?,?,?);");
					pstmt.setInt(1, userid);
					pstmt.setInt(2, 0);
					pstmt.setInt(3, 0);
					pstmt.setInt(4, 0);
					pstmt.setInt(5, 0);
					pstmt.setInt(6, 0);
					pstmt.setInt(7, 0);
					pstmt.setInt(8, 0);
					pstmt.execute();

				}
				else
				{
					tmp2=null;
				}

			} catch (Exception e) {
				e.printStackTrace();
				tmp2=null;
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}

		return tmp2;
	}
	
	//&新鲜宝	理财帐户开启
	public CrowdUserAuthClass UPDATA_User_auth_Isvruser2(int userid)  {
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(userid);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("UPDATE cd_user_auth SET isvruser=0 WHERE userid=?;");
				pstmt.setInt(1, userid);
				if(pstmt.executeUpdate()==1)
				{
					tmp2.isvruser="0";
					//理财帐户初始化
					DbConnectionManager.fastcloseStmt(pstmt);
					pstmt = con.prepareStatement("delete from vr_user_rmb where userid=?;");
					pstmt.setInt(1, userid);
					pstmt.execute();

				}
				else
				{
					tmp2=null;
				}

			} catch (Exception e) {
				e.printStackTrace();
				tmp2=null;
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}

		return tmp2;
	}	
	
	
	/**指定userid 产生模拟用户**/
	public CrowdUserAuthClass createUsernmae2(int id,String mobile,String password,String introduceId) throws LzPluginException {
		boolean abortTransaction = false;
		Connection con = null;
		PreparedStatement pstmt = null;
		CrowdUserAuthClass tmp = null;

		
		// Auth表中是否有记录
		tmp = CrowdUserAuthCache.getInstance().getUserBymobile(mobile);
		if(tmp!=null)
		{
			throw new LzPluginException("用户已存在");
		}
		// 如果没有，则新建用户Auth记录
		if (tmp == null) {


			System.out.println("注册-开始auth表1");
			
			try {
				long time = new Date().getTime();
				con = DbConnectionManager.getTransactionConnection();
				pstmt = con.prepareStatement("INSERT INTO cd_user_auth (userid,mobile,password,username,introduceId,ctime,isvruser) VALUES (?,?,?,?,?,?,?)");
				pstmt.setInt(1,id);
				pstmt.setLong(2, CacheClass.getLongValue(mobile.trim()));
				pstmt.setString(3, password);
				pstmt.setString(4, CacheClass.MobileStar(mobile));
				if (!CacheClass.isEmpty(introduceId)) {
					pstmt.setString(5, introduceId);
				} else {
					pstmt.setNull(5, Types.VARCHAR);
				}
				pstmt.setLong(6, time);
				pstmt.setInt(7, 1);
				pstmt.execute();


				// Rmb-id
				DbConnectionManager.fastcloseStmt(pstmt);
				pstmt = con.prepareStatement("INSERT INTO cd_user_rmb (userid,nowrmb ,rank) VALUES (?,?,?)");
				pstmt.setInt(1, id);
				pstmt.setFloat(2, id);
				pstmt.setInt(3, id);
				pstmt.execute();
				
				//card-id
				DbConnectionManager.fastcloseStmt(pstmt);
				pstmt = con.prepareStatement(INSERT_USER_Card);
				pstmt.setInt(1, id);
				pstmt.execute();		
				
				
				// parent-id
				if(!CacheClass.isEmpty(introduceId)){ //cd_user_parent
					
					//求得推荐人父二级的id
					CrowdUserParentClass tmp0=user_parentCacheManager.getInstance().load_user_Parent(CacheClass.getIntValue(introduceId));
					//用户关系表
					DbConnectionManager.fastcloseStmt(pstmt);
					pstmt = con.prepareStatement(INSERT_USER_parent);
					pstmt.setInt(1, id);
					pstmt.setInt(2, CacheClass.getIntValue(tmp0.userid));
					pstmt.setInt(3, CacheClass.getIntValue(tmp0.pid0));
					pstmt.setInt(4, CacheClass.getIntValue(tmp0.pid1));
					pstmt.setLong(5, time);
					pstmt.execute();		
				}
				else
				{
					//用户关系表
					DbConnectionManager.fastcloseStmt(pstmt);
					pstmt = con.prepareStatement(INSERT_USER_parent);
					pstmt.setInt(1, id);
					pstmt.setInt(2, 0);
					pstmt.setInt(3, 0);
					pstmt.setInt(4, 0);
					pstmt.setLong(5, time);
					pstmt.execute();
				}
				
				DbConnectionManager.fastcloseStmt(pstmt);
				pstmt = con.prepareStatement("INSERT INTO vr_user_rmb (userid,vrprinciplelrmb,vrcashgiftrmb1,vrcashgiftrmb2,vrnowrmb,vrnowcashgiftrmb1,vrnowcashgiftrmb2,vrpreorderrmb) VALUES (?,?,?,?,?,?,?,?)");
				pstmt.setInt(1, id);
				pstmt.setBigDecimal(2, new BigDecimal(555555555));
				pstmt.setFloat(3, 0);
				pstmt.setFloat(4, 0);
				pstmt.setBigDecimal(5, new BigDecimal(555555555));
				pstmt.setFloat(6, 0);
				pstmt.setFloat(7, 0);
				pstmt.setFloat(8, 0);
				pstmt.execute();


				System.out.println("注册-set表  ");

			} catch (Exception e) {
				tmp = null;
				abortTransaction = true;
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeTransactionConnection(pstmt, con,abortTransaction);
			}

		}
		System.out.println("注册-表 结束");
		// 注册成功后删除mobile code的记录
		if (!abortTransaction) {
			tmp = CrowdUserAuthCache.getInstance().getUserBymobile(mobile);
			if(!CacheClass.isEmpty(mobile))
				CacheClass.getMobileCodeCacheManger().deleteMobileCode(mobile);
			
			//邀请人的团队人数加1
			if(!CacheClass.isEmpty(introduceId))
			{
				userRmbDataClass uu=user_rmbCacheManager.getInstance().update_User_Mannum(CacheClass.getIntValue(introduceId));
				if(uu!=null)
					CrowdUserAuthCache.getInstance().ClearObjectCache(introduceId);	
			}
			
		}
		
		return tmp;
	}
    
	
	
	
	
	
	//金城 
	public CrowdUserAuthClass updateAuthForUid(String userid,String uid,String pwd,String applyid)  {
			
			boolean abortTransaction = false;
			CrowdUserAuthClass tmp2 = null;
			// Auth表中是否有记录
			tmp2 = CrowdUserAuthCache.getInstance().getUser(CacheClass.getIntValue(userid));
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			if (tmp2 != null) {
				try {
					con = DbConnectionManager.getTransactionConnection();
					pstmt = con.prepareStatement("UPDATE "+ authTable+ " SET uid=?,pwd=?,applyid=?,applystate=0 WHERE userid=?");
					pstmt.setString(1, uid.trim());
					pstmt.setString(2, pwd);
					pstmt.setString(3, applyid);
					pstmt.setInt(4, CacheClass.getIntValue(userid));
					
					pstmt.executeUpdate();
					tmp2.uid=uid.trim();
					tmp2.pwd=pwd;
					tmp2.applyid=applyid;
					tmp2.applystate="0";
					
				} catch (SQLException e) {
					tmp2=null;
					abortTransaction = true;
					e.printStackTrace();
				} finally {
					DbConnectionManager.closeTransactionConnection(rs,pstmt, con,abortTransaction);
				}
			}

			return tmp2;
	}
	//郑大
	public CrowdUserAuthClass updateAuthForUid(String userid,String useraccount,String pwd)  {
		
		boolean abortTransaction = false;
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(CacheClass.getIntValue(userid));
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getTransactionConnection();
				pstmt = con.prepareStatement("UPDATE "+ authTable+ " SET useraccount=?,pwd=? WHERE userid=?");
				pstmt.setString(1, useraccount);
				pstmt.setString(2, pwd);
				pstmt.setInt(3, CacheClass.getIntValue(userid));
				
				pstmt.executeUpdate();
				tmp2.useraccount=useraccount;
				tmp2.pwd=pwd;
			} catch (SQLException e) {
				tmp2=null;
				abortTransaction = true;
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeTransactionConnection(rs,pstmt, con,abortTransaction);
			}
		}

		return tmp2;
}	
	
	//&新鲜宝    开通交易帐户（金网）
	public void openAuthForTradeUser(String userid,QueryJWModelData querData) throws LzPluginException  {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String mobile=null;
		String uid=null;
		String pwd=null;
		String applyid=null;
		String applystate=null;
		
		
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select userid,mobile,applyid,applystate from "+ authTable+ " WHERE userid=?");
			pstmt.setInt(1, CacheClass.getIntValue(userid));
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				userid=rs.getString("userid");
				mobile=rs.getString("mobile");
				applyid=rs.getString("applyid");
				applystate=rs.getString("applystate");
			}
			if(CacheClass.isEmpty(mobile))
			{
				throw new LzPluginException( "无此用户");
			}
			int applystateint =CacheClass.getIntValue(applystate);
			if(!CacheClass.isEmpty(applyid) ) //此用户已开通
			{
				switch(applystateint)
				{
				case 0:
					throw new LzPluginException( "您已申请开户，请等待工作员审核。");
				case 1:
					throw new LzPluginException( "您已成功开户了。");
				case 2:
					throw new LzPluginException( "您的开户申请已审核通过，请在线下前往银行签约第三方存管");
				}
			}
			//交易所开户
			String REQName="openaccount_apply";
			String jsonStr=JSON.toJSONString(querData);
			String xmlStr=TestXml2Json.jsontoXml(jsonStr);
			xmlStr=xmlStr.replace("<MEBS_MOBILE>", "<MEBS_MOBILE><REQ name=\""+REQName+"\" >");
			xmlStr=xmlStr.replace("</MEBS_MOBILE>", "</REQ></MEBS_MOBILE>");
			ResultModel3 result=null;
			try {
				result=Api3.buildRequest(CacheClass.getMwUrl(),xmlStr);
			} catch (Exception e) {
				result=null;
				e.printStackTrace();
			}
			if(result==null || CacheClass.getIntValue(result.success)!=0)
			{
				if(result!=null)
				{
					throw new LzPluginException( "出错,注册失败,retcode="+result.success+":"+result.message);
				}
				else
				{
					throw new LzPluginException( "出错,注册失败,retcode=null");
				}
			}
			else //注册交易系统成功，用户表追加uid值
			{
				System.out.println("交易所注册成功");
				Mademd5 mad5=new Mademd5();
				String codepwd=querData.CertificateNo.substring(querData.CertificateNo.length()-6);
				codepwd=result.userId+codepwd;
				pwd=mad5.toMd5(codepwd).toLowerCase();
				
				uid=result.userId;
				applyid=result.applyId;
				CrowdUserAuthCache.getInstance().updateAuthForUid(userid,uid,pwd,applyid);
				//更新
				CrowdUserAuthCache.getInstance().ClearObjectCache(userid);
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs,pstmt, con);
		}
			
	}	
	
	
	//&新鲜宝    开通交易帐户
	public void openAuthForTradeUser(String userid) throws LzPluginException  {
			
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			String mobile=null;
			String password=null;
			String useraccount = null;
			String uid=null;
			String iscompanyid="201001";
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("select * from "+ authTable+ " WHERE userid=?");
				pstmt.setInt(1, CacheClass.getIntValue(userid));
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					userid=rs.getString("userid");
					mobile=rs.getString("mobile");
					password=rs.getString("password");
					useraccount=rs.getString("useraccount");
					uid=rs.getString("uid");
					iscompanyid=rs.getString("iscompanyid");
				}
				if(CacheClass.isEmpty(mobile))
				{
					throw new LzPluginException( "无此用户");
				}
				if(!CacheClass.isEmpty(useraccount)) //此用户已开通
				{
					return ;
				}
				
				//交易所注册
				int isZDTrade=CacheClass.getIntValue(BaseDataCacheManager.getInstance().Load_Param("isZDTrade"));
				if(isZDTrade==0)  //金城系统
				{
					QueryModel queryParameters=new QueryModel();
					queryParameters.weixinid= userid;
					queryParameters.mobile= mobile;
					queryParameters.loginpwd= DigestUtils.shaHex(password);
					queryParameters.moneypwd= DigestUtils.shaHex(password);
					queryParameters.username= CacheClass.MobileStar(mobile);
					queryParameters.idcode= userid;

					String jsonStr=JSON.toJSONString(queryParameters);
					ResultModel result=null;
					try {
						result=Api.buildRequest(CacheClass.getMwUrl()+"reg",jsonStr);
					} catch (Exception e) {
						result=null;
						e.printStackTrace();
					}
	  				if(result==null || CacheClass.getIntValue(result.retcode)!=0)
					{
						if(result!=null)
						{
							throw new LzPluginException( "出错,注册失败,retcode="+result.retcode+":"+result.GetRetMsg());
						}
						else
						{
							throw new LzPluginException( "出错,注册失败,retcode=null");
						}
					}
					else //注册交易系统成功，用户表追加uid值
					{
						System.out.println("交易所注册成功");
						CrowdUserAuthCache.getInstance().updateAuthForUid(userid,result.uid,result.useraccount,password);
						//更新
						CrowdUserAuthCache.getInstance().ClearObjectCache(userid);
					}
				}
				else //郑大系统
				{
					CrowdSmallCompanyClass obj=CrowdSmallCompanyCacheManager.getInstance().loadSmallCompanyByid(iscompanyid);
					if(obj!=null && !CacheClass.isEmpty(obj.marketparentid))
					{
						QueryZDModelData data=new QueryZDModelData();
						data.NF241FB=mobile;
						data.NF261FC=CacheClass.MobileStar(mobile);
						data.NF2Q1FD=DigestUtils.md5Hex(password).toUpperCase();
						data.NF2B1FE=mobile;
						data.NF291FF=userid;
						data.NF2H1FG=obj.marketparentid;
						
						QueryZDModel mode= new QueryZDModel();
						mode.NF2S="NF2BVK";
						mode.NF3A=data;
						mode.NF3B1FL=userid;
						
						String jsonStr=JSON.toJSONString(mode);
						
						Map<String, String> directivesVal=new HashMap<String, String>();
						directivesVal.put("directives", "["+jsonStr+"]");
						
						NameValuePair[] formParams= Api.generatNameValuePair(directivesVal);	

						ResultModel2 result=null;
						try {
							result=Api2.buildRequest(CacheClass.getMwUrl(),formParams);
						} catch (Exception e) {
							result=null;
							e.printStackTrace();
						}
						if(result==null || result.getRetCode()!=0)
						{
							if(result!=null)
							{
								throw new LzPluginException( "出错,注册失败,retcode="+result.NF3C+":"+result.NF34);
							}
							else
							{
								throw new LzPluginException( "出错,注册失败,retcode=null");
							}
						}
						else //注册交易系统成功，用户表追加uid值
						{
							System.out.println("交易所注册成功");
							CrowdUserAuthCache.getInstance().updateAuthForUid(userid,mobile,password);
							//更新
							CrowdUserAuthCache.getInstance().ClearObjectCache(userid);
						}
					}
					
				}			
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs,pstmt, con);
			}

			
			
	}		

	
	public CrowdUserAuthClass UPDATA_User_mt_token(int id,String mt_token)  {
		CrowdUserAuthClass tmp2 = null;
		// Auth表中是否有记录
		tmp2 = CrowdUserAuthCache.getInstance().getUser(id);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (tmp2 != null) {
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("UPDATE "	+ authTable	+ " SET mttoken=? WHERE userid=?");
				pstmt.setString(1, mt_token);
				pstmt.setInt(2, id);
				pstmt.executeUpdate();
				
				tmp2.mt_token=mt_token;

			} catch (Exception e) {
				e.printStackTrace();
				tmp2=null;
			} finally {
				DbConnectionManager.closeConnection(rs, pstmt, con);
			}
		}

		return tmp2;
	}
	
	
	//&新鲜宝    查询审核状态
	public int QueryUserTradeApplyState(int userid,String applyid,int oldApplystate) throws LzPluginException  
	{
		String xmlStr="<?xml version=\"1.0\" encoding = \"UTF-8\"?>"+
		"<MEBS_MOBILE>"+
			"<REQ name=\"check_status\">"+
				"<ApplyId>"+applyid+"</ApplyId>"+
			"</REQ>"+
		"</MEBS_MOBILE>";

		ResultModel3 result=null;
		try {
			result=Api3.buildRequest(CacheClass.getMwUrl(),xmlStr);
		} catch (Exception e) {
			result=null;
			e.printStackTrace();
		}
		if(result==null || CacheClass.getIntValue(result.success)<0)
		{
			if(result!=null)
			{
				throw new LzPluginException( "出错,查询失败,retcode="+result.success+":"+result.message);
			}
			else
			{
				throw new LzPluginException( "出错,查询失败,retcode=null");
			}
		}
		else //查询成功
		{
			int applystate=CacheClass.getIntValue(result.success);
			if(oldApplystate!=applystate)
			{
				//更新状态值
				Connection con = null;
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				try {
					con = DbConnectionManager.getConnection();
					pstmt = con.prepareStatement("update "+ authTable+ " set applystate=? where userid=? and applyid=?");
					pstmt.setInt(1,applystate);
					pstmt.setInt(2, userid);
					pstmt.setInt(3, CacheClass.getIntValue(applyid));
					pstmt.executeUpdate();
					
					oldApplystate=applystate;
					
					//清缓存
					CrowdUserAuthCache.getInstance().ClearObjectCache(userid+"");
					
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DbConnectionManager.closeConnection(rs,pstmt, con);
				}
			}
		}
		
		return oldApplystate;
	}	
	
	//登陆交易系统
	public String LoginAuthForTrade(String userid,boolean tochange,String codepassword) throws LzPluginException  {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sessionid=null;
		
		String uid=null;
		String pwd=null;
		String password2=null;
		
		
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("select userid,password2,uid,pwd from "+ authTable+ " WHERE userid=?");
			pstmt.setInt(1, CacheClass.getIntValue(userid));
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				userid=rs.getString("userid");
				password2=rs.getString("password2");
				uid=rs.getString("uid");
				pwd=rs.getString("pwd");
			}
			if(CacheClass.isEmpty(password2))
			{
				throw new LzPluginException( "无此用户");
			}
			
			//交易所登陆
			String xmlStr="<?xml version=\"1.0\" encoding = \"UTF-8\"?>"+
					"<MEBS_MOBILE>"+
						"<REQ name=\"logon\">"+
							"<USER_ID>"+uid+"</USER_ID>"+
							"<PASSWORD>"+pwd+"</PASSWORD>"+
							"<LA>0</LA>"+
						"</REQ>"+
					"</MEBS_MOBILE>";
			ResultModel3 result=null;
			try {
				result=Api3.buildRequest(CacheClass.getTradeUlr(),xmlStr);
			} catch (Exception e) {
				result=null;
				e.printStackTrace();
			}
			if( result==null || (result.success!=null && result.success.length()<3)  )
			{
				if(result!=null)
				{
					throw new LzPluginException( "出错,登陆失败,retcode="+result.success+":"+result.message);
				}
				else
				{
					throw new LzPluginException( "出错,登陆失败,retcode=null");
				}
			}
			else //登陆交易系统成功，更新
			{
				//更新seeionid
				DbConnectionManager.fastcloseStmt(rs,pstmt);
				pstmt = con.prepareStatement("update "+ authTable+" set mttoken=? WHERE userid=?");
				pstmt.setString(1, result.success);
				pstmt.setInt(2, CacheClass.getIntValue(userid));
				pstmt.executeUpdate();
				
				sessionid=result.success;
				
				//更新
				CrowdUserAuthCache.getInstance().ClearObjectCache(userid);
				
				if(CacheClass.getIntValue(result.changepwd)==1 || (tochange && !CacheClass.isEmpty(codepassword))) //强制修改密码
				{
					String newPwd=uid+password2;
					if(tochange && !CacheClass.isEmpty(codepassword))
					{
						newPwd=uid+codepassword;
					}
					Mademd5 mad5=new Mademd5();
					newPwd=mad5.toMd5(newPwd).toLowerCase();
					updateUserTradepwd(userid,uid,pwd,newPwd,sessionid);
				}
			}
		
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnectionManager.closeConnection(rs,pstmt, con);
		}
			
		return sessionid;
		
	}	
	
	//修改交易密码
	public boolean updateUserTradepwd(String userid,String uid,String oldpwdMd5,String newpwdMd5,String sessionid) throws LzPluginException  {
		
		boolean flag=false;
		String xmlStr="<?xml version=\"1.0\" encoding = \"UTF-8\"?>"+
				"<MEBS_MOBILE>"+
					"<REQ name=\"change_password\">"+
						"<USER_ID>"+uid+"</USER_ID>"+
						"<OLD_PASSWORD>"+oldpwdMd5+"</OLD_PASSWORD>"+
						"<NEW_PASSWORD>"+newpwdMd5+"</NEW_PASSWORD>"+
						"<SESSION_ID>"+sessionid+"</SESSION_ID>"+
					"</REQ>"+
				"</MEBS_MOBILE>";
		ResultModel3 result=null;
		try {
			result=Api3.buildRequest(CacheClass.getTradeUlr(),xmlStr);
		} catch (Exception e) {
			result=null;
			e.printStackTrace();
		}
		if(result==null || CacheClass.getIntValue(result.success)!=0)
		{
			if(result!=null)
			{
				throw new LzPluginException( "出错,修改密码失败,retcode="+result.success+":"+result.message);
			}
			else
			{
				throw new LzPluginException( "出错,修改密码失败,retcode=null");
			}
		}
		else //修改密码成功
		{
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try {
				con = DbConnectionManager.getConnection();
				pstmt = con.prepareStatement("update "+ authTable+" set pwd=? WHERE userid=?");
				pstmt.setString(1, newpwdMd5);
				pstmt.setInt(2, CacheClass.getIntValue(userid));
				pstmt.executeUpdate();
				
				flag=true;
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnectionManager.closeConnection(rs,pstmt, con);
			}
		}
			
		return flag;
		
	}
	
	
}
